﻿using System;
using System.Data;
using System.Collections.Generic;
using Npgsql;

namespace QQZiFramework.DataBase.PostgreSQL
{
    public class PostgreSQLAccess : QQZiFramework.IDBAccess
    {
        public PostgreSQLAccess(DBContext context)
           : base(context)
        {

        }
        public override void Create(string connectionString)
        {
            conStr = connectionString;
        }

        public override IDbConnection GetConnection()
        {
            return conn;
        }

        public override void BeginTransaction()
        {
            if (trans != null)
            {
                throw new Exception("有未提交的事务，无法再创建！");
            }
            OpenSql();
            trans = conn.BeginTransaction();
            mContext.LogSqlMessage("DataBase BeginTransaction", true);
        }
        public override DataTable QueryTable(string sql)
        {
            return QueryTable(sql, CommandType.Text);
        }
        public override IDataReader QueryReader(string sql, CommandType cmdType, params System.Data.IDbDataParameter[] parameters)
        {
            if (reader != null && !reader.IsClosed) throw new ClientException("有已打开的Reader，请先调用reader.close方法关闭");
            var cmd = CreateCommand(sql, cmdType, parameters, true);
            reader = cmd.ExecuteReader();
            cmd = null;
            return reader;
        }
        string lastSql;
        NpgsqlConnection conn;
        NpgsqlTransaction trans;
        //NpgsqlCommand cmd;
        NpgsqlDataReader reader;
        string conStr;

        private void OpenSql()
        {
            if (conn == null) conn = new NpgsqlConnection(conStr);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
                mContext.LogSqlMessage("******** DataBase Open (" + System.Threading.Thread.CurrentThread.ManagedThreadId + ") ********", true);
            }
        }

        private NpgsqlCommand CreateCommand(string sql, CommandType cmdType, System.Data.IDbDataParameter[] parameters, bool query)
        {
            OpenSql();
            NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
            if (conn.ConnectionTimeout > 60) cmd.CommandTimeout = conn.ConnectionTimeout;
            cmd.CommandText = sql;
            cmd.CommandType = cmdType;
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
                sql += "(";
                foreach (NpgsqlParameter parameter in parameters)
                {
                    sql += parameter.ParameterName + "=";
                    if (parameter.Value != null && parameter.Value.ToString().Length > 255) sql += "长内容";
                    else sql += parameter.Value;
                    sql += ",";
                }
                sql = sql.Substring(0, sql.Length - 1) + ")";
            }
            if (trans != null) cmd.Transaction = trans;
            lastSql = sql;
            mContext.LogSqlMessage(sql, query);
            return cmd;
        }

        public override int ExecuteCommand(string sql)
        {
            // 枚举 CommandType.text =1 , （sql语句） CommandType.StoredProcedure = 4,    （存储过程的名称）   CommandType.TableDirect = 512（表名）,
            return ExecuteCommand(sql, CommandType.Text);
        }

        public override object ExecScalar(string sql)
        {
            return ExecScalar(sql, CommandType.Text);
        }

        public override IDbDataParameter CreateParameter(string name, object value)
        {
            return new NpgsqlParameter(":" + name, value);
        }

        public override bool AotoIdentity
        {
            get { return false; }
        }

        public override Dictionary<string, string> FunctionFormatMap => throw new NotImplementedException();

        
        public override string GetNewIdentitySql(string tableName)
        {
            return null;
        }

        public override string GetInsertedIdentitySql(string tableName)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 是否存在表
        /// </summary>
        public override bool IsTableExists(string tableName)
        {
            object scalar = ExecScalar("select count(*) from information_schema.tables where table_schema='public' and table_type='BASE TABLE' and table_name='" + tableName.ToUpper() + "'");
            return 1.Equals(scalar);
        }

        public override void Dispose()
        {
            if (conn != null)
            {
                if (conn.State == ConnectionState.Open)
                {
                    if (trans != null)
                    {
                        trans.Rollback();
                        mContext.LogSqlMessage("Rollback", true);
                    }
                    if (reader != null && !reader.IsClosed) reader.Close();
                    conn.Close();
                    mContext.LogSqlMessage("-------- DataBase Close (" + System.Threading.Thread.CurrentThread.ManagedThreadId + ") --------", true);
                }
                //if (cmd != null) cmd.Dispose();
                conn.Dispose();
                conn = null;
                // cmd = null;
                trans = null;
            }
        }


        public override string GetLastSql()
        {
            return lastSql;
        }

        public override void Commit()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                if (trans != null)
                {
                    trans.Commit();
                    trans = null;
                    mContext.LogSqlMessage("DataBase Commit", true);
                }
            }
        }
        public override void Rollback()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    trans = null;
                    mContext.LogSqlMessage("DataBase Rollback", true);
                }
            }
        }
        public override void Close()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                if (reader != null && !reader.IsClosed) reader.Close();
                if (trans != null)
                {
                    trans.Commit();
                    trans = null;
                    mContext.LogSqlMessage("DataBase Commit", true);
                }
            }
            Dispose();
        }


        public override String PartQuerySql(string select, string table, string where, string orderby, int start, int end)
        {
            if (orderby == null) throw new ArgumentNullException("pagger query order by");
            string sql;
            if (start == 1 && false)
            {
                //第一页的查询使用top (这里不应用，因为row_number跟order排序出来不一样，会照成第一页的数据不准)
                sql = "select top " + (end - start + 1) + "  " + select + " from " + table;
                if (!string.IsNullOrEmpty(where)) sql += " where " + where;
                sql += " order by " + orderby;
            }
            else
            {
                sql = "select * from (select row_number() over(order by " + orderby + ") rownum, " + select + " from " + table;
                if (!string.IsNullOrEmpty(where)) sql += " where " + where;
                sql += ") t where rownum between " + start + " and " + end;
            }
            return sql;
        }

        // public override String PartQuerySql(string select, string table, string where, string orderby, IDbDataParameter pageIndex, IDbDataParameter pageSize){
        //      if (orderby == null) throw new ArgumentNullException("pagger query order by");
        //       string sql = "select " + select + " from " + table;
        //     if (!string.IsNullOrEmpty(where)) sql += " where " + where;
        //     sql += " order by " + orderby;
        //     sql += " limit " + pageIndex.ParameterName + "-1 * "+ pageSize.ParameterName +"," + pageSize.ParameterName;
        //     return sql;
        // } 


        public override DataTable QueryTable(string text, CommandType cmdType, params System.Data.IDbDataParameter[] parameters)
        {
            var cmd = CreateCommand(text, cmdType, parameters, true);
            cmd.CommandType = cmdType;
            System.Data.DataTable dt = new DataTable();
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
            da.Fill(dt);
            return dt;
        }

        public override int ExecuteCommand(string text, CommandType cmdType, params IDbDataParameter[] parameters)
        {
            var cmd = CreateCommand(text, cmdType, parameters, false);
            cmd.CommandType = cmdType;
            return cmd.ExecuteNonQuery();
        }

        public override object ExecScalar(string text, CommandType cmdType, params System.Data.IDbDataParameter[] parameters)
        {
            var cmd = CreateCommand(text, cmdType, parameters, text.IndexOf("insert", StringComparison.OrdinalIgnoreCase) == -1);
            return cmd.ExecuteScalar();
        }

        public override string GetDateConverter(int length)
        {
            return "Convert(varchar(" + length + "),{0},120)";
        }

        public override void CreateTreeExtendTableIfNotExists(string treeTableName)
        {
            ExecuteCommand("if not exists ( select  id from sysobjects where name = 'A0_UserAgencys_REX' and type = 'U' ) Create TABLE " + treeTableName + "_REX( NodeId int  not null,     Deep int not null,     SubNodeId int not Null)");
        }

        public override void BulkInsert(string tableName, DataTable dataTable)
        {
            throw new NotImplementedException();
        }
        /// <summary>
        /// 获取属性的类型转为数据库中定义的类型
        /// </summary>
        public override string ConvertDataType(Type propertyType, bool withSize)
        {

            switch (propertyType.Name)
            {
                case "Int32":
                case "Int64":
                case "Single":
                case "Double":
                case "Decimal":
                    return "NUMBER";
                case "DateTime":
                    return "date";
                default:
                    if (withSize) return "VARCHAR2(255)";
                    else return "VARCHAR2";
            }
        }


        /// <summary>
        /// 创建临时表（通常是根据DataTable)，返回表名（包含#）
        /// <param name="tableName">临时表名，不包含#</param>
        /// <param name="allColumns">所有字段</param>
        /// <param name="createPrimaryKey">指定某个列名为主键，可为空</param>
        /// </summary>
        public override string CreateTempTable(string tableName, DataColumn[] allColumns, DataColumn createPrimaryKey)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 根据临时表的内容，更新物理表
        /// <param name="primaryKey">主键名</param>
        /// <param name="updateColumns">要更新的列</param>
        /// </summary>
        public override void UpdateByTempTable(string tableName, string tempTableFullName, DataColumn[] updateColumns, DataColumn primaryKeyColumn)
        {
            throw new NotImplementedException();
        }


        /// <summary>
        /// 删除临时表
        /// </summary>
        public override void DropTempTable(string tableName)
        {
            throw new NotImplementedException();
        }

        public override bool IsColumnExists(string tableName, string columnName)
        {
            throw new NotImplementedException();
        }

        public override string[] GetTablePrimaryKeys(string tableName)
        {
            throw new NotImplementedException();
        }

        public override Dictionary<string, string> GetTables()
        {
            throw new NotImplementedException();
        }

        public override ForeignKeyItem[] GetTableForeignKeys(string tableName)
        {
            throw new NotImplementedException();
        }
    }
}
